package data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import ui.MessagePopUp;

/** Represents all db_user related DAObjects
 * inserting, updating, deleting data in the table from here.
 * @author Matthias
 */
public class BankDatabaseUserSQL {
	
	/** Declaring the BankDatabase object **/
	private final BankDatabase bankDatabase;
	
	/** Constructor of BankDatabaseUserSQL
	 * @param bankDatabase is the BankDatabase object
	 */
	public BankDatabaseUserSQL(final BankDatabase bankDatabase) {
		this.bankDatabase = bankDatabase;
	}

	/** Inserting user into the table db_user
	 * @param userID id of user
	 * @param pin pin of user
	 * @param fName first name of user
	 * @param lName last name of user
	 * @param telephoneNr telephone number of user
	 * @param streetName street name of user
	 * @param streetNr street number of user
	 * @param zipCode zip code of user
	 * @param city city of user
	 * @throws SQLException if an SQL error occurs
	 */
	public final void insertUserIntoTable (final String userID, final int pin,
			final String fName, final String lName, final String telephoneNr,
			final String streetName, final String streetNr,
			final String zipCode, final String city) throws SQLException {

		Connection conn = BankDatabase.getDBConnection();
		PreparedStatement preparedStatement = null;

		String insertUserSQL = "INSERT INTO db_user"
				+ "(User_ID, PIN, FIRSTNAME, LASTNAME, Telephone_Number,"
				+ " STREETNAME, STREETNR, ZIPCODE, CITY, CREATED_DATE) VALUES"
				+ "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			preparedStatement = conn.prepareStatement(insertUserSQL);
			preparedStatement.setString(1, userID);
			preparedStatement.setInt(2, pin);
			preparedStatement.setString(3, fName);
			preparedStatement.setString(4, lName);
			preparedStatement.setString(5, telephoneNr);
			preparedStatement.setString(6, streetName);
			preparedStatement.setString(7, streetNr);
			preparedStatement.setString(8, zipCode);
			preparedStatement.setString(9, city);
			preparedStatement.setTimestamp(10, BankDatabase.getCurrentTimeStamp());
			preparedStatement.executeUpdate();

			// parsing the id to a int, in order to use it for the arraylist
			int id = Integer.parseInt(userID);
			// add user to the ArrayList in order to keep the database sync with arraylist
			bankDatabase.addUser(new User(id, pin, fName, lName, 
					telephoneNr, streetName, streetNr, zipCode, city));

			String text = "The user was successfully created!";
			new MessagePopUp().setSuccess(text);
		}
		catch (SQLException exc) {
			String text = "Problems occured, " 
					+ "inserting the user. Please try again";
			new MessagePopUp().setError(text);
		}
		finally {
			preparedStatement.close();
			conn.close();
		}
	}
	
	/** Editing a user in the db
	 * @param userID id of user
	 * @param pin pin of user
	 * @param fName first name of user
	 * @param lName last name of user
	 * @param telephoneNr telephone number of user
	 * @param streetName street name of user
	 * @param streetNr street number of user
	 * @param zipCode zip code of user
	 * @param city city of user
	 * @throws SQLException if an SQL error occurs
	 */
	public final void editUserFromTable(final String userID, final int pin, 
			final String fName, final String lName,
			final String telephoneNr, final String streetName, 
			final String streetNr, final String zipCode,
			final String city) throws SQLException {
		
		Connection conn = BankDatabase.getDBConnection();
		Statement stmt = conn.createStatement();

		String editUserSQL = "UPDATE DB_User SET User_ID=" + userID
		+ ",PIN=" + pin + ",Firstname='" + fName + "',Lastname='" + lName
		+ "',Telephone_Number=" + telephoneNr + ",Streetname='" + streetName
		+ "',Streetnr=" + streetNr + ", Zipcode=" + zipCode + ", City='" + city
		+ "' WHERE User_ID=" + bankDatabase.getClassATM().getSelectedUserID();

		try {
			stmt.executeUpdate(editUserSQL);
			// parsing the id to a int, in order to use it for the arraylist
			int id = Integer.parseInt(userID);
			// updating the arraylist userList
			bankDatabase.getUserList().get(bankDatabase.getClassATM().getClassScreen().getAdmEdit().
					getComboBox().getSelectedIndex()).setNewInfo(
					id, pin, fName, lName, telephoneNr,
					streetName, streetNr, zipCode, city);

			String text = "The user was successfully edited!";
			new MessagePopUp().setSuccess(text);
		}
		catch(Exception exc){
			String text = "Problems occured, editing the user. Please try again";
			new MessagePopUp().setError(text);
		}
		finally {
			stmt.close();
			conn.close();
		}
	}
	
	/** Deletes a user from the table
	 * @param userID id of user
	 * @throws SQLException if an SQL error occurs
	 */
	public final void deleteUserFromTable(final int userID) throws SQLException {

		Connection conn = BankDatabase.getDBConnection();
		PreparedStatement preparedStatement = null;

		String deleteUserSQL = "DELETE FROM DB_User "
				+ "WHERE User_ID = "+userID;

		try {
			preparedStatement = conn.prepareStatement(deleteUserSQL);
			// execute delete SQL statement
			preparedStatement.executeUpdate();
			// deletes User from arraylist in order to keep it sync
			bankDatabase.deleteUser(userID);

			String text = "The user " + userID 
					+ "\nis deleted from the database!";
			new MessagePopUp().setSuccess(text);
		}
		catch (SQLException exc) {
			String text = "Problems occured, " 
					+ "deleting the user. Please try again";
			new MessagePopUp().setError(text);
		}
		finally {
			preparedStatement.close();
			conn.close();
		}
	}

	/** Viewing the database in a jtable
	 * @param query is the SQL syntax that is given to the method,
	 * and being retrieved in the database
	 * @return the data from the connected sql database to the jtable
	 * @throws SQLException
	 */
	public final ui.ResultSetTableModel viewDB(final String query) throws SQLException {

		Connection conn = null;
		ResultSet rs;
		Statement stmt = null;
		
		try {
			conn = BankDatabase.getDBConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(query);
			return new ui.ResultSetTableModel(rs);
		} 
		catch (SQLException e) {
			String text = "Problems occured with database. Please try again";
			new MessagePopUp().setError(text);
		} 
		finally {}
		return null;
	}

} // end this class